import pandas as pd
import pymysql
from pymysql.cursors import DictCursor

class MysqlUtils(object):
    """数据库工具类"""
    def __init__(self):
        self.conn = pymysql.connect(
            host='localhost',
            port=3306,
            user='root',
            password='root',  # 注意：实际使用时请替换为你的数据库密码
            db='scenic',  # 第一张图中是scenic库，第三张图中是tushare库，这里需根据实际需求调整或区分
            charset='utf8'
        )
    
    def get_data(self):
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = """
        SELECT 
        t.tourist_agency_name, rel.id_no, LEFT(rel.id_no, 2) as province_code, DAYOFWEEK(gate.create_time) as weekend,
        cast(substring(rel.id_no, 7, 4) as unsigned) as birth_year
        FROM ticket_order t 
        JOIN ticket_order_gate_rel o on t.id = rel.order_id
        JOIN gate_ticket_rel g on o.gate_ticket_rel_id = rel.id
        WHERE t.tourist_agency_name != '' and t.pay_time != ''
        """
        cursor.execute(sql)
        data = cursor.fetchall()
        df = pd.DataFrame(data)
        print(df.head())

        # 数据清洗
        df['non_weekend'] = df['weekend'].apply(lambda x: 1 if x not in [1, 7] else 0)  # 1表示非周末，0表示周末
        df['valid_id'] = df['id_no'].apply(lambda x: 1 if len(x.split('-')) == 1 else 0)  # 1表示有效，0表示无效
        # 计算有标记的老年人及外省游客
        df['elderly_ratio'] = df.apply(
            lambda x: 1 if (x['valid_id'] and 2025 - x['birth_year'] > 60) else 0 if x['valid_id'] else np.nan,
            axis=1
        )
        df['out_province'] = df.apply(
            lambda x: 1 if (x['valid_id'] and x['province_code'] != '44') else 0 if x['valid_id'] else np.nan,
            axis=1
        )

        # 分组聚合计算占比
        result = df.groupby('tourist_agency_name').agg({
            'valid_id': 'count',  # 总游客数py
            'non_weekend': 'mean',  # 非周末占比
            'elderly_ratio': 'mean',  # 老年人占比
            'out_province': 'mean'  # 外省游客占比
        })
        print(result)
        return df

class Classification(object):
    """分类工具类"""
    def __init__(self):
        pass   
    def get_fina_indicator(self, conn):
        """获取财务数据"""
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        query = """
        SELECT ts_code, ann_date, eps, total_revenue_ps, undist_profit_ps, gross_margin, fcff, fcfe, tangible_asset, bps,
        grossprofit_margin, npta FROM financial_data WHERE financial_data.ann_date BETWEEN '2023-01-01' and '2024-01-01'
        """
        cursor.execute(query)
        result = cursor.fetchall()
        df = pd.DataFrame(result)
        # 处理缺失值
        df = df.dropna(subset=['eps', 'total_revenue_ps', 'undist_profit_ps', 'gross_margin', 'fcff', 'fcfe', 'tangible_asset', 'bps',
                              'grossprofit_margin', 'npta'])
        # 重置索引
        df = df.reset_index(drop=True)
        return df

if __name__ == '__main__':
    # 处理景区数据
    mysql_utils_scenic = MysqlUtils()
    scenic_df = mysql_utils_scenic.get_data()

    # 处理财务数据（需注意：MysqlUtils的__init__中数据库为scenic，若财务数据在tushare库，需调整MysqlUtils或新建类）
    # 此处为示例，若财务数据在不同数据库，需修改MysqlUtils的数据库连接逻辑
    # mysql_utils_tushare = MysqlUtils()
    # mysql_utils_tushare.conn.select_db('tushare')  # 切换数据库
    # classification = Classification()
    # fina_df = classification.get_fina_indicator(mysql_utils_tushare.conn)
    # print(fina_df.head())
